import pandas as pd

try:
    # 读取 Excel 文件
    file_path = r'stockdata.xlsx'
    df = pd.read_excel(file_path, dtype={'股票代码':'str'})  # 确保数据类型正确
except FileNotFoundError:
    print(f"错误：找不到文件,请确认文件位置。")
except Exception as e:
    print(f"读取 Excel 文件时发生错误：{e}")

if 'df' in locals():  # 检查是否成功读取到数据并创建了 df
    try:
        # 将日期列转换为年份
        df['年份'] = pd.to_datetime(df['日期']).dt.year

        # 按年度和股票代码分组，计算短期负债、长期负债和资产负债率的年度均值
        annual_df = df.groupby(['股票代码', '年份', '实际控制人性质', '行业代码']).agg({
            '短期负债':'mean',
            '长期负债合计':'mean',
            '资产负债率':'mean'
        }).reset_index()

        # 填充缺失值
        annual_df.loc[:, '短期负债'] = annual_df['短期负债'].fillna(annual_df['短期负债'].mean())
        annual_df.loc[:, '长期负债合计'] = annual_df['长期负债合计'].fillna(annual_df['长期负债合计'].mean())
        annual_df.loc[:, '资产负债率'] = annual_df['资产负债率'].fillna(annual_df['资产负债率'].mean())

        # 重命名“实际控制人性质”列为“所有权”
        annual_df.rename(columns={'实际控制人性质': '所有权'}, inplace=True)
    except KeyError as e:
        print(f"错误：找不到列 {e}。请确认 Excel 文件中包含正确的列名。")
    except Exception as e:
        print(f"处理数据时发生错误：{e}")

    if 'annual_df' in locals():  # 检查是否成功创建了 annual_df
        try:
            # 保存为新的 Excel 文件
            output_file_path = r'C:\Users\Lenovo\PycharmProjects\pythonProject1\a2.xlsx'
            annual_df.to_excel(output_file_path, index=False)
            print(f"年度数据表已保存为 '{output_file_path}'")
        except Exception as e:
            print(f"保存 Excel 文件时发生错误：{e}")
else:
    print("读取数据失败，无法进行后续操作。")